insert overwrite table jms_dm.dm_third_party_out_warehouse_dt partition(dt)
select to_date(tab_in_warehouse.scan_time)  as in_warehouse_date,           --入库日期
       sys_network.agent_code               as dispatch_network_agent_code, --派件网点代理区编码
       sys_network.agent_name               as dispatch_network_agent_name, --派件网点代理区名称
       oms_waybill.dispatch_network_code    as dispatch_network_code,       --派件网点编码
       oms_waybill.dispatch_network_name    as dispatch_network_name,       --派件网点名称
       tab_in_warehouse.waybill_no          as billcode,                    --运单号
       tab_in_warehouse.post_station_code   as store_code,                  --门店编码
       tab_in_warehouse.storage_address     as store_name,                  --门店名称
       tab_in_warehouse.oper_source_code    as brand_code,                  --品牌名称id
       tab_in_warehouse.partner_name        as brand_name,                  --名牌名称
       tab_in_warehouse.scan_user_code      as scanuser_code,               --扫描员编码
       tab_in_warehouse.scan_user           as scanuser,                    --入库扫描的扫描员
       tab_in_warehouse.scan_time           as in_warehouse_time,           --入库扫描的时间
       tab_out_warehouse.scan_time          as out_warehouse_time,          --出库扫描的时间
       tab_in_warehouse.destination         as pickup_code,                 --取件码
       tab_take_out.scan_time               as take_out_time,               --快件取出扫描时间
       tab_barscan_return.notify_message    as notify_message,              --短信通知消息
       tab_barscan_return.send_status       as send_status,                 --短信通知状态
       tab_barscan_return.message_send_time as message_send_time,           --短信通知发送的时间
       tab_barscan_deliver.scan_time        as deliver_scan_time,           --出仓扫描时间
       to_date(tab_in_warehouse.scan_time)  as dt
from (--分区时间inputtime   取扫描时间,录入时间要多往后取一天
         select waybill_no,
                post_station_code,                                                             --门店编码
                storage_address,                                                               --门店名称
                oper_source_code,                                                              --品牌名称id
                partner_name,                                                                  --名牌名称
                scan_user_code,                                                                --扫描员编码
                scan_user,                                                                     --入库扫描的扫描员
                scan_time,                                                                     --入库扫描的扫描时间
                concat(substr(destination, 0, length(destination) - 4), '****') as destination --取件码
         from jms_dwd.dwd_tab_barscan_collect_base_dt
         where dt = '{{ execution_date | cst_ds }}'
           and scan_type = '入库扫描'
           and to_date(scan_time) = '{{ execution_date | cst_ds }}'
     ) tab_in_warehouse
         left join
     (
         select waybill_no
              , scan_site_code
              , scan_time
         from (
                  select waybill_no
                       , scan_site_code
                       , scan_time
                       , row_number() over (partition by waybill_no,scan_site_code order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_collect_base_dt
                  where dt = '{{ execution_date | cst_ds }}'
                    and scan_type = '快件取出扫描'
              ) a
         where rn = 1
     ) tab_take_out
     on tab_in_warehouse.waybill_no = tab_take_out.waybill_no
         left join
     (
         select waybill_no
              , max(scan_time) as scan_time
         from jms_dwd.dwd_tab_barscan_collect_base_dt
         where dt = '{{ execution_date | cst_ds }}'
         group by waybill_no
     ) tab_out_warehouse
     on tab_in_warehouse.waybill_no = tab_out_warehouse.waybill_no
         left join
     (--疑问
         select waybill_no
              , scan_site_code
              , scan_time
         from (
                  select waybill_no
                       , scan_site_code
                       , scan_time
                       , row_number() over (partition by waybill_no,scan_site_code order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_deliver_base_dt
                  where dt = '{{ execution_date | cst_ds }}'
              ) a
         where rn = 1
     ) tab_barscan_deliver
     on tab_in_warehouse.waybill_no = tab_barscan_deliver.waybill_no
         left join
     (
         select waybill_no
              , notify_message
              , send_status
              , message_send_time
         from (
                  select waybill_no,
                         regexp_replace(notify_message, '[0-9]', '*')                                as notify_message,
                         send_status,
                         message_send_time,
                         row_number() over (partition by waybill_no order by message_send_time desc) as rn
                  from jms_dwd.dwd_tab_barscan_return_msg_base_dt
                  where dt = '{{ execution_date | cst_ds }}'
              ) a
         where rn = 1
     ) tab_barscan_return
     on tab_in_warehouse.waybill_no = tab_barscan_return.waybill_no
         left join
     (
         select waybill_no
              , dispatch_network_code
              , dispatch_network_name
         from (
                  select waybill_no
                       , dispatch_network_code
                       , dispatch_network_name
                       , row_number()
                          over (partition by waybill_no,dispatch_network_code order by input_time desc) as rn
                  from jms_dwd.dwd_yl_oms_oms_waybill_dt
                  where dt = '{{ execution_date | cst_ds }}'
              ) a
         where rn = 1
     ) oms_waybill
     on tab_in_warehouse.waybill_no = oms_waybill.waybill_no
         left join
     (
         select code
              , agent_code
              , agent_name
         from jms_dim.dim_network_whole_massage
     ) sys_network
     on coalesce(oms_waybill.dispatch_network_code, oms_waybill.waybill_no) = sys_network.code
distribute by dt,pmod(hash(billcode),10)
;